﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Gift.DBUtility;//Please add references
namespace Gift.DAL
{
    /// <summary>
    /// 数据访问类:DALGoods
    /// </summary>
    public partial class DALGoods
    {
        public DALGoods()
        { }
        #region  Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbGoods");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Gift.Model.tbGoods model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tbGoods(");
            strSql.Append("category,brand,goodsType,goodsName,goodsNo,origin,isBest,isNew,isTopSelling,isShelves,isPromote,isUserDiscount,isFromFreight,freightTemplate,isOrderFromFreight,isDiscount,isTotalReduce,isGifts,isSendIntegral,keywords,description,goodsRemark,creator,createTime,modifyTime,companyId,isProfShelves,professionType)");
            strSql.Append(" values (");
            strSql.Append("@category,@brand,@goodsType,@goodsName,@goodsNo,@origin,@isBest,@isNew,@isTopSelling,@isShelves,@isPromote,@isUserDiscount,@isFromFreight,@freightTemplate,@isOrderFromFreight,@isDiscount,@isTotalReduce,@isGifts,@isSendIntegral,@keywords,@description,@goodsRemark,@creator,@createTime,@modifyTime,@companyId,@isProfShelves,@professionType)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@category", SqlDbType.NVarChar,500),
					new SqlParameter("@brand", SqlDbType.Int,4),
					new SqlParameter("@goodsType", SqlDbType.Int,4),
					new SqlParameter("@goodsName", SqlDbType.NVarChar,50),
					new SqlParameter("@goodsNo", SqlDbType.NVarChar,50),
					new SqlParameter("@origin", SqlDbType.NVarChar,100),
					new SqlParameter("@isBest", SqlDbType.Bit,1),
					new SqlParameter("@isNew", SqlDbType.Bit,1),
					new SqlParameter("@isTopSelling", SqlDbType.Bit,1),
					new SqlParameter("@isShelves", SqlDbType.Bit,1),
					new SqlParameter("@isPromote", SqlDbType.Bit,1),
					new SqlParameter("@isUserDiscount", SqlDbType.Bit,1),
					new SqlParameter("@isFromFreight", SqlDbType.Bit,1),
					new SqlParameter("@freightTemplate", SqlDbType.Int,4),
					new SqlParameter("@isOrderFromFreight", SqlDbType.Bit,1),
					new SqlParameter("@isDiscount", SqlDbType.Bit,1),
					new SqlParameter("@isTotalReduce", SqlDbType.Bit,1),
					new SqlParameter("@isGifts", SqlDbType.Bit,1),
					new SqlParameter("@isSendIntegral", SqlDbType.Bit,1),
					new SqlParameter("@keywords", SqlDbType.NVarChar,100),
					new SqlParameter("@description", SqlDbType.NVarChar,200),
					new SqlParameter("@goodsRemark", SqlDbType.Text),
					new SqlParameter("@creator", SqlDbType.Int,4),
					new SqlParameter("@createTime", SqlDbType.DateTime),
					new SqlParameter("@modifyTime", SqlDbType.DateTime),
					new SqlParameter("@companyId", SqlDbType.Int,4),
                    new SqlParameter("@isProfShelves", SqlDbType.Bit,1),
            	    new SqlParameter("@professionType", SqlDbType.Int,4)};
            parameters[0].Value = model.category;
            parameters[1].Value = model.brand;
            parameters[2].Value = model.goodsType;
            parameters[3].Value = model.goodsName;
            parameters[4].Value = model.goodsNo;
            parameters[5].Value = model.origin;
            parameters[6].Value = model.isBest;
            parameters[7].Value = model.isNew;
            parameters[8].Value = model.isTopSelling;
            parameters[9].Value = model.isShelves;
            parameters[10].Value = model.isPromote;
            parameters[11].Value = model.isUserDiscount;
            parameters[12].Value = model.isFromFreight;
            parameters[13].Value = model.freightTemplate;
            parameters[14].Value = model.isOrderFromFreight;
            parameters[15].Value = model.isDiscount;
            parameters[16].Value = model.isTotalReduce;
            parameters[17].Value = model.isGifts;
            parameters[18].Value = model.isSendIntegral;
            parameters[19].Value = model.keywords;
            parameters[20].Value = model.description;
            parameters[21].Value = model.goodsRemark;
            parameters[22].Value = model.creator;
            parameters[23].Value = model.createTime;
            parameters[24].Value = model.modifyTime;
            parameters[25].Value = model.CompanyId;
            parameters[26].Value = model.isProfShelves;
            parameters[27].Value = model.professionType;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Gift.Model.tbGoods model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbGoods set ");
            strSql.Append("category=@category,");
            strSql.Append("brand=@brand,");
            strSql.Append("goodsType=@goodsType,");
            strSql.Append("goodsName=@goodsName,");
            strSql.Append("goodsNo=@goodsNo,");
            strSql.Append("origin=@origin,");
            strSql.Append("isBest=@isBest,");
            strSql.Append("isNew=@isNew,");
            strSql.Append("isTopSelling=@isTopSelling,");
            strSql.Append("isShelves=@isShelves,");
            strSql.Append("isProfShelves=@isProfShelves,");
            strSql.Append("isPromote=@isPromote,");
            strSql.Append("isUserDiscount=@isUserDiscount,");
            strSql.Append("isFromFreight=@isFromFreight,");
            strSql.Append("freightTemplate=@freightTemplate,");
            strSql.Append("isOrderFromFreight=@isOrderFromFreight,");
            strSql.Append("isDiscount=@isDiscount,");
            strSql.Append("isTotalReduce=@isTotalReduce,");
            strSql.Append("isGifts=@isGifts,");
            strSql.Append("isSendIntegral=@isSendIntegral,");
            strSql.Append("keywords=@keywords,");
            strSql.Append("description=@description,");
            strSql.Append("goodsRemark=@goodsRemark,");
            strSql.Append("creator=@creator,");
            strSql.Append("createTime=@createTime,");
            strSql.Append("modifyTime=@modifyTime,");
            strSql.Append("professionType=@professionType");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@category", SqlDbType.NVarChar,500),
					new SqlParameter("@brand", SqlDbType.Int,4),
					new SqlParameter("@goodsType", SqlDbType.Int,4),
					new SqlParameter("@goodsName", SqlDbType.NVarChar,50),
					new SqlParameter("@goodsNo", SqlDbType.NVarChar,50),
					new SqlParameter("@origin", SqlDbType.NVarChar,100),
					new SqlParameter("@isBest", SqlDbType.Bit,1),
					new SqlParameter("@isNew", SqlDbType.Bit,1),
					new SqlParameter("@isTopSelling", SqlDbType.Bit,1),
					new SqlParameter("@isShelves", SqlDbType.Bit,1),
                    new SqlParameter("@isProfShelves", SqlDbType.Bit,1),
					new SqlParameter("@isPromote", SqlDbType.Bit,1),
					new SqlParameter("@isUserDiscount", SqlDbType.Bit,1),
					new SqlParameter("@isFromFreight", SqlDbType.Bit,1),
					new SqlParameter("@freightTemplate", SqlDbType.Int,4),
					new SqlParameter("@isOrderFromFreight", SqlDbType.Bit,1),
					new SqlParameter("@isDiscount", SqlDbType.Bit,1),
					new SqlParameter("@isTotalReduce", SqlDbType.Bit,1),
					new SqlParameter("@isGifts", SqlDbType.Bit,1),
					new SqlParameter("@isSendIntegral", SqlDbType.Bit,1),
					new SqlParameter("@keywords", SqlDbType.NVarChar,100),
					new SqlParameter("@description", SqlDbType.NVarChar,200),
					new SqlParameter("@goodsRemark", SqlDbType.Text),
					new SqlParameter("@creator", SqlDbType.Int,4),
					new SqlParameter("@createTime", SqlDbType.DateTime),
					new SqlParameter("@modifyTime", SqlDbType.DateTime),
                    new SqlParameter("@professionType", SqlDbType.Int,4),
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.category;
            parameters[1].Value = model.brand;
            parameters[2].Value = model.goodsType;
            parameters[3].Value = model.goodsName;
            parameters[4].Value = model.goodsNo;
            parameters[5].Value = model.origin;
            parameters[6].Value = model.isBest;
            parameters[7].Value = model.isNew;
            parameters[8].Value = model.isTopSelling;
            parameters[9].Value = model.isShelves;
            parameters[10].Value = model.isProfShelves;
            parameters[11].Value = model.isPromote;
            parameters[12].Value = model.isUserDiscount;
            parameters[13].Value = model.isFromFreight;
            parameters[14].Value = model.freightTemplate;
            parameters[15].Value = model.isOrderFromFreight;
            parameters[16].Value = model.isDiscount;
            parameters[17].Value = model.isTotalReduce;
            parameters[18].Value = model.isGifts;
            parameters[19].Value = model.isSendIntegral;
            parameters[20].Value = model.keywords;
            parameters[21].Value = model.description;
            parameters[22].Value = model.goodsRemark;
            parameters[23].Value = model.creator;
            parameters[24].Value = model.createTime;
            parameters[25].Value = model.modifyTime;
            parameters[26].Value = model.professionType;
            parameters[27].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbGoods ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbGoods ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Gift.Model.tbGoods GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,category,brand,goodsType,goodsName,goodsNo,origin,isBest,isNew,isTopSelling,isShelves,isPromote,isUserDiscount,isFromFreight,freightTemplate,isOrderFromFreight,isDiscount,isTotalReduce,isGifts,isSendIntegral,keywords,description,goodsRemark,creator,createTime,modifyTime,companyId,isnull(isProfShelves,0) as isProfShelves,isnull(professionType,0) as professionType from tbGoods ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            Gift.Model.tbGoods model = new Gift.Model.tbGoods();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["category"] != null && ds.Tables[0].Rows[0]["category"].ToString() != "")
                {
                    model.category = ds.Tables[0].Rows[0]["category"].ToString();
                }
                if (ds.Tables[0].Rows[0]["brand"] != null && ds.Tables[0].Rows[0]["brand"].ToString() != "")
                {
                    model.brand = int.Parse(ds.Tables[0].Rows[0]["brand"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsType"] != null && ds.Tables[0].Rows[0]["goodsType"].ToString() != "")
                {
                    model.goodsType = int.Parse(ds.Tables[0].Rows[0]["goodsType"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsName"] != null && ds.Tables[0].Rows[0]["goodsName"].ToString() != "")
                {
                    model.goodsName = ds.Tables[0].Rows[0]["goodsName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsNo"] != null && ds.Tables[0].Rows[0]["goodsNo"].ToString() != "")
                {
                    model.goodsNo = ds.Tables[0].Rows[0]["goodsNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["origin"] != null && ds.Tables[0].Rows[0]["origin"].ToString() != "")
                {
                    model.origin = ds.Tables[0].Rows[0]["origin"].ToString();
                }
                if (ds.Tables[0].Rows[0]["isBest"] != null && ds.Tables[0].Rows[0]["isBest"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isBest"].ToString() == "1") || (ds.Tables[0].Rows[0]["isBest"].ToString().ToLower() == "true"))
                    {
                        model.isBest = true;
                    }
                    else
                    {
                        model.isBest = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isNew"] != null && ds.Tables[0].Rows[0]["isNew"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isNew"].ToString() == "1") || (ds.Tables[0].Rows[0]["isNew"].ToString().ToLower() == "true"))
                    {
                        model.isNew = true;
                    }
                    else
                    {
                        model.isNew = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isTopSelling"] != null && ds.Tables[0].Rows[0]["isTopSelling"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isTopSelling"].ToString() == "1") || (ds.Tables[0].Rows[0]["isTopSelling"].ToString().ToLower() == "true"))
                    {
                        model.isTopSelling = true;
                    }
                    else
                    {
                        model.isTopSelling = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isShelves"] != null && ds.Tables[0].Rows[0]["isShelves"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isShelves"].ToString() == "1") || (ds.Tables[0].Rows[0]["isShelves"].ToString().ToLower() == "true"))
                    {
                        model.isShelves = true;
                    }
                    else
                    {
                        model.isShelves = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isProfShelves"] != null && ds.Tables[0].Rows[0]["isProfShelves"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isProfShelves"].ToString() == "1") || (ds.Tables[0].Rows[0]["isProfShelves"].ToString().ToLower() == "true"))
                    {
                        model.isProfShelves = true;
                    }
                    else
                    {
                        model.isProfShelves = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isPromote"] != null && ds.Tables[0].Rows[0]["isPromote"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isPromote"].ToString() == "1") || (ds.Tables[0].Rows[0]["isPromote"].ToString().ToLower() == "true"))
                    {
                        model.isPromote = true;
                    }
                    else
                    {
                        model.isPromote = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isUserDiscount"] != null && ds.Tables[0].Rows[0]["isUserDiscount"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isUserDiscount"].ToString() == "1") || (ds.Tables[0].Rows[0]["isUserDiscount"].ToString().ToLower() == "true"))
                    {
                        model.isUserDiscount = true;
                    }
                    else
                    {
                        model.isUserDiscount = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isFromFreight"] != null && ds.Tables[0].Rows[0]["isFromFreight"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isFromFreight"].ToString() == "1") || (ds.Tables[0].Rows[0]["isFromFreight"].ToString().ToLower() == "true"))
                    {
                        model.isFromFreight = true;
                    }
                    else
                    {
                        model.isFromFreight = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["freightTemplate"] != null && ds.Tables[0].Rows[0]["freightTemplate"].ToString() != "")
                {
                    model.freightTemplate = int.Parse(ds.Tables[0].Rows[0]["freightTemplate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["isOrderFromFreight"] != null && ds.Tables[0].Rows[0]["isOrderFromFreight"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isOrderFromFreight"].ToString() == "1") || (ds.Tables[0].Rows[0]["isOrderFromFreight"].ToString().ToLower() == "true"))
                    {
                        model.isOrderFromFreight = true;
                    }
                    else
                    {
                        model.isOrderFromFreight = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isDiscount"] != null && ds.Tables[0].Rows[0]["isDiscount"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isDiscount"].ToString() == "1") || (ds.Tables[0].Rows[0]["isDiscount"].ToString().ToLower() == "true"))
                    {
                        model.isDiscount = true;
                    }
                    else
                    {
                        model.isDiscount = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isTotalReduce"] != null && ds.Tables[0].Rows[0]["isTotalReduce"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isTotalReduce"].ToString() == "1") || (ds.Tables[0].Rows[0]["isTotalReduce"].ToString().ToLower() == "true"))
                    {
                        model.isTotalReduce = true;
                    }
                    else
                    {
                        model.isTotalReduce = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isGifts"] != null && ds.Tables[0].Rows[0]["isGifts"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isGifts"].ToString() == "1") || (ds.Tables[0].Rows[0]["isGifts"].ToString().ToLower() == "true"))
                    {
                        model.isGifts = true;
                    }
                    else
                    {
                        model.isGifts = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isSendIntegral"] != null && ds.Tables[0].Rows[0]["isSendIntegral"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isSendIntegral"].ToString() == "1") || (ds.Tables[0].Rows[0]["isSendIntegral"].ToString().ToLower() == "true"))
                    {
                        model.isSendIntegral = true;
                    }
                    else
                    {
                        model.isSendIntegral = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["keywords"] != null && ds.Tables[0].Rows[0]["keywords"].ToString() != "")
                {
                    model.keywords = ds.Tables[0].Rows[0]["keywords"].ToString();
                }
                if (ds.Tables[0].Rows[0]["description"] != null && ds.Tables[0].Rows[0]["description"].ToString() != "")
                {
                    model.description = ds.Tables[0].Rows[0]["description"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsRemark"] != null && ds.Tables[0].Rows[0]["goodsRemark"].ToString() != "")
                {
                    model.goodsRemark = ds.Tables[0].Rows[0]["goodsRemark"].ToString();
                }
                if (ds.Tables[0].Rows[0]["creator"] != null && ds.Tables[0].Rows[0]["creator"].ToString() != "")
                {
                    model.creator = int.Parse(ds.Tables[0].Rows[0]["creator"].ToString());
                }
                if (ds.Tables[0].Rows[0]["createTime"] != null && ds.Tables[0].Rows[0]["createTime"].ToString() != "")
                {
                    model.createTime = DateTime.Parse(ds.Tables[0].Rows[0]["createTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["modifyTime"] != null && ds.Tables[0].Rows[0]["modifyTime"].ToString() != "")
                {
                    model.modifyTime = DateTime.Parse(ds.Tables[0].Rows[0]["modifyTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["companyId"] != null && ds.Tables[0].Rows[0]["companyId"].ToString() != "")
                {
                    model.CompanyId = int.Parse(ds.Tables[0].Rows[0]["companyId"].ToString());
                }
                model.professionType = int.Parse(ds.Tables[0].Rows[0]["professionType"].ToString());
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,category,brand,goodsType,goodsName,goodsNo,origin,isBest,isNew,isTopSelling,isShelves,isPromote,isUserDiscount,isFromFreight,freightTemplate,isOrderFromFreight,isDiscount,isTotalReduce,isGifts,isSendIntegral,keywords,description,goodsRemark,creator,createTime,modifyTime ");
            strSql.Append(" FROM tbGoods ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,category,brand,goodsType,goodsName,goodsNo,origin,isBest,isNew,isTopSelling,isShelves,isPromote,isUserDiscount,isFromFreight,freightTemplate,isOrderFromFreight,isDiscount,isTotalReduce,isGifts,isSendIntegral,keywords,description,goodsRemark,creator,createTime,modifyTime ");
            strSql.Append(" FROM tbGoods ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM tbGoods ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from tbGoods T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }


        public DataTable GetAllGoods()
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select *from tbgoods ", new object[0]);
    
                table = DbHelperSQL.Query(commandText).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetAllGoodsByTerm(string term)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select id '商品序号',max(case when rank=1 then category else '' end) '商品分类',max(case when rank=1 then ltrim(brand) else '' end) '品牌',max(case when rank=1 then goodsName else '' end) '商品名称',max(case when rank=1 then goodsNo else '' end) '商品编号',max(case when rank=1 then origin else '' end) '产地',max(case when rank=1 then ltrim(isBest) else '' end) '是否精品',max(case when rank=1 then ltrim(isNew) else '' end) '是否新品',max(case when rank=1 then ltrim(isTopSelling) else '' end) '是否上架',max(case when rank=1 then ltrim(isShelves) else '' end) '是否热销',max(case when rank=1 then ltrim(isPromote) else '' end) '是否打折促销',max(case when rank=1 then ltrim(isUserDiscount) else '' end) '是否参与会员打折',max(case when rank=1 then ltrim(isOrderFromFreight) else '' end) '是否订单满额免运费',max(case when rank=1 then ltrim(isDiscount) else '' end) '是否订单满额打折',max(case when rank=1 then ltrim(isTotalReduce) else '' end) '是否订单满额减少',max(case when rank=1 then ltrim(isGifts) else '' end) '是否订单满额赠送礼品',max(case when rank=1 then ltrim(isSendIntegral) else '' end) '是否订单满额赠送积分',max(case when rank=1 then ltrim(isFromFreight) else '' end) '是否免运费',max(case when rank=1 then ltrim(freightTemplate) else '' end) '运费模版',geId '销售序号',barCode '商品条形码',goodsArtno '商品货号',salePrice '销售价格',marketPrice '市场价格',costPrice '成本价格',giveIntegral '购买商品赠送积分',goodsWeight '重量', weightUnit '单位',inventory '库存'from(select tbgoods.id,category,brand,goodsName,goodsNo,origin,isBest,isNew,isTopSelling,isShelves,isPromote,isUserDiscount,isFromFreight,freightTemplate,isOrderFromFreight,isDiscount,isTotalReduce,isGifts,isSendIntegral,tbgoodsEntitys.id as geId,goods,barCode,goodsArtno,salePrice,marketPrice,costPrice,giveIntegral,goodsWeight,weightUnit,inventory,rank from (select *,row_number() over(partition by goods order by getdate()) rank from tbgoodsEntitys) tbgoodsEntitys join tbgoods on tbgoods.id=tbgoodsEntitys.goods " + ((term == "") ? "" : ("where " + term)) + ") tt group by goods,id,geId,barCode,goodsArtno,salePrice,marketPrice,costPrice,giveIntegral,goodsWeight,weightUnit,inventory", new object[0]);
                table = DbHelperSQL.Query(commandText).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsByCategoryAndIsShelvesAndIsBestAndTop(string category, int isShelves, int isBest, int top)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,goodsNo,goodsName,marketPrice,salePrice,thumbImg,imgAlt from view_googsEntitys where isShelves=@isShelves and isBest=@isBest and id in(select goods from tbgoodsCategory a left join tbcategory b on a.category=b.categoryIdentity where b.id=@category or parentId=@category or parentIdStr like @parentId group by goods) order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@top", top), new SqlParameter("@category", category), new SqlParameter("@parentId", "%" + category + "%"), new SqlParameter("@isShelves", isShelves), new SqlParameter("@isBest", isBest) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsByCategoryAndIsShelvesAndIsNewAndTop(string category, int isShelves, int top, int isNew)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,goodsNo,goodsName,marketPrice,salePrice,thumbImg,imgAlt from view_googsEntitys where isShelves=@isShelves and isNew=@isNew and id in(select goods from tbgoodsCategory a left join tbcategory b on a.category=b.categoryIdentity where b.id=@category or parentId=@category or parentIdStr like @parentId group by goods) order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@top", top), new SqlParameter("@category", category), new SqlParameter("@parentId", "%" + category + "%"), new SqlParameter("@isShelves", isShelves), new SqlParameter("@isNew", isNew) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsByCompanyAndIsShelvesAndIsNewAndTop(int companyId, int isShelves, int top, int isNew)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,goodsNo,goodsName,marketPrice,salePrice,thumbImg,imgAlt from view_googsEntitys where companyId = @companyId and isShelves=@isShelves and isNew=@isNew and id in(select goods from tbgoodsCategory a left join tbcategory b on a.category=b.categoryIdentity  group by goods) order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@top", top), new SqlParameter("@companyId", companyId),  new SqlParameter("@isShelves", isShelves), new SqlParameter("@isNew", isNew) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsByCategoryAndIsShelvesAndIsPromoteAndTop(string category, int isShelves, int top, int isPromote)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,goodsName,marketPrice,salePrice,thumbImg,imgAlt from view_googsEntitys where isShelves=@isShelves and isPromote=@isPromote and id in(select goods from tbgoodsCategory a left join tbcategory b on a.category=b.categoryIdentity where b.id=@category or b.parentId=@category or parentIdStr like @parentId group by goods) order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@top", top), new SqlParameter("@category", category), new SqlParameter("@parentId", "%" + category + "%"), new SqlParameter("@isShelves", isShelves), new SqlParameter("@isPromote", isPromote) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsByCategoryAndIsShelvesAndTop(string category, int isShelves, int top)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,goodsNo,goodsName,marketPrice,salePrice,thumbImg,imgAlt from view_googsEntitys where isShelves=@isShelves and id in(select goods from tbgoodsCategory a left join tbcategory b on a.category=b.categoryIdentity where b.id=@category or b.parentId=@category or parentIdStr like @parentId group by goods) order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@top", top), new SqlParameter("@category", category), new SqlParameter("@parentId", "%" + category + "%"), new SqlParameter("@isShelves", isShelves) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsByGoodsAndIsTopSellingAndIsShelvesAndTop(int goods, int isTopSelling, int isShelves, int top)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,goodsName,marketPrice,salePrice,thumbImg,imgAlt,scoreNumber,scores from view_googsEntitys where category in(select category from view_googsEntitys where id=@goods) and id<>@goods and isTopSelling=@isTopSelling and isShelves=@isShelves  order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@goods", goods), new SqlParameter("@top", top), new SqlParameter("@isTopSelling", isTopSelling), new SqlParameter("@isShelves", isShelves) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsById(int id)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select id,category,brand,goodsType,goodsName,goodsNo,origin,isBest,isNew,isTopSelling,isShelves,isPromote,isUserDiscount,isFromFreight,freightTemplate,isOrderFromFreight,isDiscount,isTotalReduce,isGifts,isSendIntegral,keywords,description,goodsRemark,isnull(isProfShelves,0) as isProfShelves,isnull(professionType,0) as professionType from tbgoods where id=@id", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@id", id) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsByIsIntegralAndIsShelvesAndTop(int isIntegral, int isShelves, int top)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,geid,goodsName,integral,thumbImg,imgAlt from view_googsEntitys where isIntegral=@isIntegral and isShelves=@isShelves  order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@top", top), new SqlParameter("@isIntegral", isIntegral), new SqlParameter("@isShelves", isShelves) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsByIsNewAndIsShelvesAndTop(int isNew, int isShelves, int top)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,goodsNo,goodsName,marketPrice,salePrice,thumbImg,imgAlt from view_googsEntitys where isNew=@isNew and isShelves=@isShelves order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@top", top), new SqlParameter("@isNew", isNew), new SqlParameter("@isShelves", isShelves) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsByIsPromoteAndIsShelvesAndTop(int isPromote, int isShelves, int top)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,goodsName,marketPrice,salePrice,thumbImg,imgAlt,scoreNumber,scores from view_googsEntitys where isPromote=@isPromote and isShelves=@isShelves  order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@top", top), new SqlParameter("@isPromote", isPromote), new SqlParameter("@isShelves", isShelves) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetGoodsByIsTopSellingAndIsShelvesAndTop(int isTopSelling, int isShelves, int top)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,goodsName,marketPrice,salePrice,thumbImg,imgAlt,scoreNumber,scores from view_googsEntitys where isTopSelling=@isTopSelling and isShelves=@isShelves  order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@top", top), new SqlParameter("@isTopSelling", isTopSelling), new SqlParameter("@isShelves", isShelves) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetRecentBrowseGoodsByIsShelvesAndTop(string goodsList, int isShelves, int top)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,goodsName,marketPrice,salePrice,thumbImg,imgAlt,scoreNumber,scores from view_googsEntitys where id in(" + goodsList + ")  and isShelves=@isShelves  order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@top", top), new SqlParameter("@isShelves", isShelves) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetShelvesGoodsById(int id)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select a.id,category,brand,brandName,goodsType,goodsName,goodsNo,origin,isFromFreight,freightTemplate,isOrderFromFreight,isDiscount,isTotalReduce,isGifts,isSendIntegral,keywords,description,goodsRemark,(select count(*) from tbcommodityScore where goods=a.id) as scoreNumber,Convert(decimal(10,1),(Convert(decimal(10,1),(select sum(scores) from tbcommodityScore where goods=a.id))/(select count(*) from tbcommodityScore where goods=a.id))) as scores from tbgoods a left join tbbrand b on a.brand=b.id where a.id=@id and isShelves=1", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@id", id) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetUserBrowseGoodsByGoodsAndIsShelvesAndTop(int goods, int isShelves, int top)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select top(@top) id,goodsName,marketPrice,salePrice,thumbImg,imgAlt,scoreNumber,scores from view_googsEntitys where id in(select goods from tbuserBrowse where [user] in(select [user] from tbuserBrowse where goods=@goods) and goods<>@goods group by goods)  and isShelves=@isShelves  order by createTime desc", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@goods", goods), new SqlParameter("@top", top), new SqlParameter("@isShelves", isShelves) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataSet GetListForProfession(string iProfessionAreaTypeId, int top)
        {
            StringBuilder strSql = new StringBuilder();
            string sTop = "";
            string sIndex = "";
            if (top != 0)
            {
                sTop = " top " + top.ToString();
            }

            strSql.Append("select distinct " + sTop + " S.*,ge.inventory,gg.smallImg,gg.thumbImg,ge.salePrice "
                         + " from tbGoods S join tbProfessionAreaType P on S.professionType = P.Id join tbCardType C on P.Id = C.ProfessionAreaTypeId ");
            strSql.Append(" join tbGoodsEntitys ge on s.id=ge.goods and isnull(ge.inventory,0) >0  join tbGoodGallery gg on gg.goods = S.id ");
            strSql.Append(" where  S.isProfShelves=1 " + sIndex);

            if (!string.IsNullOrEmpty(iProfessionAreaTypeId) && iProfessionAreaTypeId != "-1")
            {
                strSql.Append(" and P.Id = " + iProfessionAreaTypeId);
            }


            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetListForClassId(string sClassId, int top, bool isIndex)
        {
            StringBuilder strSql = new StringBuilder();
            string sTop = "";
            string sIndex = "";
            if (top != 0)
            {
                sTop = " top " + top.ToString();
            }
            if (isIndex)
            {
                sIndex = " and isnull(proisnew1,0) = 1";
            }
            strSql.Append("select distinct " + sTop + " * from DB_Shoppro ");
            strSql.Append(" where  visible = 1 ");
            strSql.Append(" and isnull(proku1,0)>0 ");
            strSql.Append(sIndex);

            if (!string.IsNullOrEmpty(sClassId))
            {
                strSql.Append(" and typeid in (select PC.classid from DB_Productclass PC join DB_Shoppro S on PC.classid = S.typeid and PC.parentid !=0 and PC.visible=1 and PC.parentid= " + sClassId + ")");
            }

            strSql.Append(" order by proorder ");


            return DbHelperSQL.Query(strSql.ToString());
        }


        public DataTable GetMarketCardMenu(int iType, int top)
        {
            string sType = "";
            string sTop = "";
            if (iType == 3)
            {
                sType = " and proisnew3 = 1";
            }
            else if (iType == 4)
            {
                sType = " and proisnew4 = 1";
            }
            if (top != 0)
            {
                sTop = " top " + top.ToString();
            }
            string sql = "select distinct " + sTop + " classid,classname,parentid,classpic,classorder from DB_Productclass where  visible=1 and parentid = 0  "
                    + " and classid in (select PC.parentid from DB_Productclass PC join DB_Shoppro S on PC.classid = S.typeid and PC.parentid !=0 and PC.visible=1 " + sType + ")"
                    + " and  parentid=0 and visible=1 order by classorder";
            return DbHelperSQL.Query(sql).Tables[0];
        }

        public DataTable GetMarketCardSubMenu(int iType, int top)
        {
            string sTop = "";
            string sType = "";
            if (iType == 3)
            {
                sType = " and proisnew3 = 1";
            }
            else if (iType == 4)
            {
                sType = " and proisnew4 = 1";
            }
            if (top != 0)
            {
                sTop = " top " + top.ToString();
            }
            string sql = "select distinct " + sTop + " classid,classname,parentid,classorder from DB_Productclass where  visible=1 and parentid != 0  "
                    + " and classid in (select PC.classid from DB_Productclass PC join DB_Shoppro S on PC.classid = S.typeid and PC.parentid !=0 and PC.visible=1 " + sType + ")"
                    + " and  parentid != 0 and visible=1 order by classorder";
            return DbHelperSQL.Query(sql).Tables[0];
        }





     

        #endregion  Method
    }
}

